libname bond 'D:\name your local directory';


***************************************************************************************************************************************************
Data management
***************************************************************************************************************************************************;

%LET IN_DATA= bondret;
%LET OUT1=LTB;
%LET OUT2=LTBRoll36_24;


data LTB;
set bond.&IN_DATA;
yymm=year(date)*100+month(date);
yyyymm=input(put(YYMM, 6.),yymmn6.); *numeric to date;
format yyyymm yymmn6.; informat yyyymm yymmn6.;
rename yyyymm=Date;
drop yymm;
source='WRDS';
c_cusip=substr(cusip, 1, 8);
return=RET_EOM*100; *return in percentage using RET_EOM;
rename c_cusip=complete_cusip;
rename Issue_id=Issuer_id;
rename RATING_NUM=rating;
rename PRICE_EOM=price;
rename TMT=Tmaturity;
AmountOut=AMOUNT_OUTSTANDING/1000; 
rename company_symbol=issuer;
keep yyyymm coupon c_cusip price_eom AmountOut issue_id rating_num return TMT company_symbol source;
run;



proc sort data=LTB;
by complete_cusip date;
run;


data temp1;
set LTB;
by complete_cusip;
cnt+1;
if first.complete_cusip then cnt=1;
run;

proc sort data=temp1;
by complete_cusip descending cnt;
run;


data temp2;
set temp1;
by complete_cusip;
retain totcnt;
if first.complete_cusip then totcnt=cnt;
if totcnt>=24;
output;
drop cnt;
run;


data bond.&out1;
set temp2;
run;


****************************************************************************************************
Creat the rolling datasets 
****************************************************************************************************;

proc sort data=temp2 (keep=date complete_cusip issuer Return) out=temp3;
by complete_cusip date;
run;


data firstdate;
set temp3;
by complete_cusip date;
if first.complete_cusip;
firstdate = intnx('month', date, 0, 'end'); FORMAT firstdate yymmn6.;
keep date complete_cusip issuer firstdate;
run;

data lastdate;
set temp3;
by complete_cusip date;
if last.complete_cusip;
lastdate = intnx('month', date, 0, 'end'); FORMAT lastdate yymmn6.;
keep date complete_cusip issuer lastdate;
run;


data firstlast;
merge firstdate lastdate;
by complete_cusip;
keep complete_cusip issuer firstdate lastdate;
run;


proc sql;
create table cusiprankdate (rename= (Return=ReturnDate)) as select a.*, b.firstdate, b.lastdate
from temp3 as a left join firstlast as b
on a.complete_cusip=b.complete_cusip;
quit;


%let nroll=36;


data cusiprankdateNew;
retain date complete_cusip issuer rankdate i ReturnDate firstdate lastdate;
set cusiprankdate;
i=1;
rankdate=intnx('month', date, -1); *the first rankdate is the date one month prior, can do rankdate=date;
do while (i<=&nroll);
   output;
   rankdate=intnx('month', rankdate, 0)-1;
   i=i+1;
end;
format rankdate yymmn6.; informat rankdate yymmn6.;
run;

proc sort data=cusiprankdateNew; by complete_cusip date; run;


data temp4;
set temp2;
yyyymm=year(date)*100+month(date);
run;

data temp5;
set cusiprankdateNew;
yyyymm=year(rankdate)*100+month(rankdate);
run;


proc sql;
create table LTBRoll as select a.*, b.issuer_id, b.return, b.TMaturity, b.Rating, b.AmountOut 
from temp5 as a left join temp4 as b
on a.complete_cusip=b.complete_cusip and a.yyyymm=b.yyyymm;
quit;


proc sort data=LTBRoll;
by complete_cusip date descending rankdate;
run;



data cnt;
retain MISS;
set LTBRoll;
by complete_cusip date;
MISS=nmiss(Return);
ym=year(date)*100+month(date);
run;


proc means data=cnt noprint;
by complete_cusip date;
output out=cnt1 sum(MISS)=MISS ;
run;


data cnt2_TR;
set cnt1;
NOBS=&nroll-MISS;
drop _type_ _freq_;
ym=year(date)*100+month(date);
if ym>200207;
if NOBS>=24;
run;


data cnt2;
set cnt2_TR;
run;

proc sort data=cnt2;
by complete_cusip ym;
run;



data cnt3 (drop=miss ym);
merge cnt (in=a) cnt2 (in=b);
by complete_cusip ym;
if a and b;
run;


proc sort data=cnt3 out=bond.&out2;
by complete_cusip date descending rankdate;
run;




